Key Compressed Indexes

개요

특징
1. 테이블이 빈번하게 수정되는 시스템, read-only 또는 semi read-only 시스템에서도 동작
2. 대용량 데이터에 compress index를 사용하면 좋다. 그러나, 트랜잭션이 조금만 증가해도 안되고 오히려 실제 운영하면 느릴 수 있다.
3. Oracle 8i N.F.
4. 인덱스 생성시에 COMPRESS OPTION 사용 가능
5. COMPRESS OPTION : 인덱스 키 컬럼의 중복을 방지해서 저장 공간의 낭비를 막아줌
제약 사항
1. B*TREE 인덱스와 IOT 에 대해서 적용 가능
2. NON-PARTITIONED INDEX 에 대해서만 적용 가능
3. UNIQUE : 컬럼이 2개 이상 적용 가능
4. NON-UNIQUE : 1개의 컬럼에 대해서도 적용 가능
원리
- 인덱스 구조 : nocompress Non-Unique index on a que_form_name column
- Before compress
- After compressed : 인덱스를 PREFIX 와 SUFFIX 두 부분으로 나누어 저장
- PREFIX : 정수로 길이 표시. 압축될 PREFIX 컬럼의 갯수임
  Prefix
prefix : 공통 부분으로 모든 SUFFIX 부분에 의해서 공유 = 각각의 저장 공간에 보다 많은 KEY 값을 저장
(on) 압축을 하기 전보다 더 적은 블럭을 읽고도 INDEX RANGE SCAN 가능
PREFIX 대상 기준
1. NON-UNIQUE 인덱스 : 모든 컬럼이 대상
2. UNIQUE 인덱스 : 한 컬럼을 제외한 모든 컬럼
  cf) SUFFIX : UNIQUE KEY로 사용
KEY 압축
- 블럭 단위로 수행
- 대상 : LEAF BLOCK
단점 : <PREFIX, SUFFIX> 를 해석하기 위해서 인덱스 SCAN 자체의 성능은 약간 저하 됨

COMPRESS 옵션을 줄때 범위를 명시하지 않으면, 마지막 컬럼을 제외한 모든 컬럼에 대해 압축 수행

테스트

UNIQUE KEY 에 대한 INDEX 압축
1. COMPRESS_UNIQUE_EMP 인덱스
  1. prefix part = ENAME KEY
  2. suffix part = EMPNO
  3. 각각의 블럭에서 ENAME은 EMPNO 항목에 의해서 공유 됨(즉, 중복된 ENAME 은 한번만 저정)
2. 샘플 테이블 생성 및 인덱스 생성


CREATE TABLE emp_compress
(EMPNO NUMBER UNIQUE, ENAME VARCHAR2(10), JOB VARCHAR2(9),
MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER);

CREATE UNIQUE INDEX COMPRESS_UNIQUE_EMP ON EMP_compress(ENAME, EMPNO)
COMPRESS 1;

-# NON-UNIQUE KEY 에 대한 INDEX 압축
-## 오라클이 자동으로 row id 컬럼을 생성
-## 생성된 row id 컬럼 = SUFFIX 부분
-## NON-UNIQUE 컬럼 = PREFIX 부분 = DEPTNO
-## NON-UNIQUE COMPRESS INDEX 생성


CREATE INDEX COMPRESS_NON_UNIQUE_EMP on EMP_compress(DEPTNO) COMPRESS;

-# 인덱스 정보 확인


SQL> select index_name, uniqueness, compression from user_indexes;

INDEX_NAME	UNIQUENESS	COMPRESSION
SYS_C005707	UNIQUE	DISABLED
BIG_TABLE_PK	UNIQUE	DISABLED
BIG_TABLE_OWNER_IDX	NONUNIQUE	DISABLED
PK_EMP	UNIQUE	DISABLED
{}COMPRESS_UNIQUE_EMP{}	{}UNIQUE{}	{}ENABLED{}
{}COMPRESS_NON_UNIQUE_EMP{}	{}NONUNIQUE{}	{}ENABLED{}
PK_DEPT	UNIQUE	DISABLED


SQL> analyze index COMPRESS_NON_UNIQUE_EMP validate structure;
SQL> select * from index_stats where name like '%NON_UNIQUE_EMP';
SQL> analyze index COMPRESS_NON_UNIQUE_EMP validate structure;
SQL> select * from index_stats where name like '%UNIQUE_EMP';

-# COMPRESS 상태 전환


SQL> ALTER INDEX COMPRESS_NON_UNIQUE_EMP REBUILD NOCOMPRESS;

SQL> ALTER INDEX COMPRESS_UNIQUE_EMP REBUILD NOCOMPRESS;

SQL> select index_name, uniqueness, compression from user_indexes;

INDEX_NAME	UNIQUENESS	COMPRESSION
SYS_C005707	UNIQUE	DISABLED
BIG_TABLE_PK	UNIQUE	DISABLED
BIG_TABLE_OWNER_IDX	NONUNIQUE	DISABLED
PK_EMP	UNIQUE	DISABLED
{}COMPRESS_UNIQUE_EMP{}	{}UNIQUE{}	{}DISABLED{}
{}COMPRESS_NON_UNIQUE_EMP{}	{}NONUNIQUE{}	{}DISABLED{}
PK_DEPT	UNIQUE	DISABLED


SQL> select * from index_stats where name like '%NON_UNIQUE_EMP';
SQL> select * from index_stats where name like '%UNIQUE_EMP';

-## user_indexes 조회 결과

OPTION	Compress = Enable	Compress = Enable	Compress = Disable	Compress = Disable
{}NAME{}	COMPRESS_NON_UNIQUE_EMP	COMPRESS_UNIQUE_EMP	COMPRESS_NON_UNIQUE_EMP	COMPRESS_UNIQUE_EMP
{}USED_SPACE{}	{}181{}	{}363{}	{}196{}	{}279{}
{}PRE_ROWS{}	3	14	0	0
{}PRE_ROWS_LEN{}	27	168	0	0
{}ROWS_PER_KEY{}	4.667	1	4.667	1
{}PCT_USED{}	3	5	3	4
{}BTREE_SPACE{}	7992	7992	7996	7996
{}USED_SPACE_Ratio{}	2.26	4.54	2.45	3.49
{}LF_ROWS{}	14	14	14	14
{}LF_ROWS_LEN{}	154	195	196	279
{}BR_ROWS{}	0	0	0	0
{}BR_ROWS_LEN{}	0	0	0	0
{}DEL_LF_ROWS{}	0	0	0	0
{}DEL_LF_ROWS_LEN{}	0	0	0	0
{}DISTINCT_KEYS{}	3	14	3	14
{}OPT_CMPR_PCTSAVE{}	0	23	7	0

COMPRESS MODE의 USED_SPACE가 압축되지 않은 상태 보다 6% 정도 절약
PRE_ROWS : 압축된 경우 PREFIXED ROW 의 갯수
PRE_ROWS_LEN : 사용된 공간

-# Index Only Table 을 COMPRESS OPTION 을 사용해서 생성하는 예제
--# IOT 테이블 생성


CREATE TABLE EMP_COM_IOT
(EMPNO NUMBER UNIQUE, ENAME VARCHAR2(10), JOB VARCHAR2(9),
MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2),
DEPTNO NUMBER, PRIMARY KEY (ENAME, EMPNO)) ORGANIZATION INDEX COMPRESS;

---# PREFIX 부분 : ENAME(첫 번째 컬럼)
---# DEFAULT : 첫번째 컬럼이 PREFIX 부분으로 잡힘


\\ \\
--# EMP_COM_IOT INDEX 구조
!EMP_COM_IOT_INDEX.jpg!
\\ \\
--# 인덱스 생성 및 분석
SQL> CREATE INDEX IOT_COM_NON_UNIQUE_EMP on EMP_COM_IOT(DEPTNO) COMPRESS;

SQL> ALTER TABLE EMP_COM_IOT MOVE COMPRESS;

SQL> select index_name, uniqueness, compression, num_rows from user_indexes;

SQL> ALTER INDEX IOT_COM_NON_UNIQUE_EMP REBUILD COMPRESS;

SQL> analyze index IOT_COM_NON_UNIQUE_EMP validate structure;

SQL> analyze index SYS_C005709 validate structure;

SQL> analyze index SYS_IOT_TOP_53121 validate structure;

SQL> select * from index_stats;

--# COMPRESS 상태 전환


SQL> ALTER TABLE EMP_COM_IOT MOVE NOCOMPRESS;

SQL> select index_name, uniqueness, compression, num_rows from user_indexes;

SQL> ALTER INDEX IOT_COM_NON_UNIQUE_EMP REBUILD COMPRESS;

SQL> analyze index IOT_COM_NON_UNIQUE_EMP validate structure;

SQL> analyze index SYS_C005709 validate structure;

SQL> analyze index SYS_IOT_TOP_53121 validate structure;

SQL> select * from index_stats;

(on) SYS_IOT_TOP_53121, SYS_C005709 rebuild 하면 에러 발생

--# user_indexes 조회 결과

출처 : OTN포럼

문서에 대하여

최초작성자 : 박혜은
최초작성일 : 2009년 11월 26일
이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

Key Compressed Indexes (by joang77) [2009.11.26]

Key Compressed Indexes

개요

문서에 대하여